package pl.streamsoft.test.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import pl.streamsoft.test.model.entities.CurrencyAverageEntity;
import pl.streamsoft.test.model.entities.CurrencyEntity;
import pl.streamsoft.test.model.entities.CurrencyTypeEntity;
import pl.streamsoft.test.model.entities.DateEntryEntity;
import pl.streamsoft.test.model.exceptions.DAOException;

public class CurrenciesJDBC extends DatabaseStore implements CurrenciesDAO {

	public void beginTransaction() throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("BEGIN;");
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		}
	}

	public void commitTransaction() throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("COMMIT;");
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		}
	}

	public void rollbackTransaction() throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("ROLLBACK;");
			statement.execute();
			statement.close();
		} catch (SQLException e) {
			throw new DAOException(e.getMessage());
		}
	}

	public CurrencyEntity insert(CurrencyEntity entity) throws DAOException {
		try {
			if (entity.getType() == null)
				throw new DAOException(
						"Inserting failed. No currency type defined");
			if (entity.getDate() == null)
				throw new DAOException("Inserting failed. No date defined");

			PreparedStatement statement = getPreparedStatementHardLocked("INSERT INTO currency(value, currency_type_id, date_entry_id) VALUES(?, ?, ?)  RETURNING id");
			statement.setDouble(1, entity.getValue());
			statement.setInt(2, entity.getType().getId());
			statement.setInt(3, entity.getDate().getId());

			ResultSet dbResult = statement.executeQuery();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
			} else {
				throw new DAOException("Id of persisted object not found");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Inserting failed "
					+ e.getLocalizedMessage());
		}
	}

	public CurrencyEntity update(CurrencyEntity entity) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("UPDATE currency SET value=?, currency_type_id=?, date_entry_id=? WHERE id = ?");
			statement.setDouble(1, entity.getValue());
			statement.setInt(2, entity.getType().getId());
			statement.setInt(3, entity.getDate().getId());
			statement.setInt(4, entity.getId());

			statement.execute();
			statement.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Updating failed " + e.getLocalizedMessage());
		}
	}

	public CurrencyEntity delete(CurrencyEntity entity) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("DELETE FROM currency WHERE id = ?");
			statement.setInt(1, entity.getId());

			statement.execute();
			statement.close();
			entity.setId(null);
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Deleting failed " + e.getLocalizedMessage());
		}
	}

	public CurrencyEntity findCurrency(Integer id) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT id, value, currency_type_id, date_entry_id FROM currency WHERE id = ?");
			statement.setInt(1, id);

			ResultSet dbResult = statement.executeQuery();
			CurrencyEntity entity = new CurrencyEntity();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
				entity.setValue(dbResult.getDouble(2));

				DateEntryEntity dateEntryEntity = findDateEntry(dbResult
						.getInt(4));
				CurrencyTypeEntity currencyTypeEntity = findCurrencyType(dbResult
						.getInt(3));
				entity.setDate(dateEntryEntity);
				entity.setType(currencyTypeEntity);

			} else {
				throw new DAOException("Query failed");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public List<CurrencyEntity> findAllCurrencies(CurrencyTypeEntity typeEntity)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT currency.id, currency.value, currency.currency_type_id, currency.date_entry_id FROM currency "
					+ "LEFT JOIN date_entry ON currency.date_entry_id = date_entry.id "
					+ "WHERE currency_type_id = ? ");
			statement.setInt(1, typeEntity.getId());

			ResultSet dbResult = statement.executeQuery();
			List<CurrencyEntity> list = new ArrayList<CurrencyEntity>();
			while (dbResult.next()) {
				CurrencyEntity entity = new CurrencyEntity();
				entity.setId(dbResult.getInt(1));
				entity.setValue(dbResult.getDouble(2));

				DateEntryEntity dateEntryEntity = findDateEntry(dbResult
						.getInt(4));
				CurrencyTypeEntity currencyTypeEntity = findCurrencyType(dbResult
						.getInt(3));
				entity.setDate(dateEntryEntity);
				entity.setType(currencyTypeEntity);

				list.add(entity);
			}
			statement.close();
			dbResult.close();
			return list;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public List<CurrencyEntity> findAllCurrencies(
			CurrencyTypeEntity typeEntity, int limit, int offset)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT currency.id, currency.value, currency.currency_type_id, currency.date_entry_id FROM currency "
					+ "LEFT JOIN date_entry ON currency.date_entry_id = date_entry.id "
					+ "WHERE currency_type_id = ? LIMIT ? OFFSET ?");
			statement.setInt(1, typeEntity.getId());
			statement.setInt(2, limit);
			statement.setInt(3, offset);

			ResultSet dbResult = statement.executeQuery();
			List<CurrencyEntity> list = new ArrayList<CurrencyEntity>();
			while (dbResult.next()) {
				CurrencyEntity entity = new CurrencyEntity();
				entity.setId(dbResult.getInt(1));
				entity.setValue(dbResult.getDouble(2));

				DateEntryEntity dateEntryEntity = findDateEntry(dbResult
						.getInt(4));
				CurrencyTypeEntity currencyTypeEntity = findCurrencyType(dbResult
						.getInt(3));
				entity.setDate(dateEntryEntity);
				entity.setType(currencyTypeEntity);

				list.add(entity);
			}
			statement.close();
			dbResult.close();
			return list;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public List<CurrencyEntity> findCurrenciesByDate(Date from, Date to)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT currency.id, currency.value, currency.currency_type_id, currency.date_entry_id FROM currency "
					+ "LEFT JOIN date_entry ON currency.date_entry_id = date_entry.id "
					+ "WHERE date_entry.date between ? and ? ");
			statement.setDate(1, new java.sql.Date(from.getTime()));
			statement.setDate(2, new java.sql.Date(to.getTime()));

			ResultSet dbResult = statement.executeQuery();
			List<CurrencyEntity> list = new ArrayList<CurrencyEntity>();
			while (dbResult.next()) {
				CurrencyEntity entity = new CurrencyEntity();
				entity.setId(dbResult.getInt(1));
				entity.setValue(dbResult.getDouble(2));

				DateEntryEntity dateEntryEntity = findDateEntry(dbResult
						.getInt(4));
				CurrencyTypeEntity currencyTypeEntity = findCurrencyType(dbResult
						.getInt(3));
				entity.setDate(dateEntryEntity);
				entity.setType(currencyTypeEntity);

				list.add(entity);
			}
			statement.close();
			dbResult.close();
			return list;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public List<CurrencyEntity> findCurrenciesByDateAndType(Date from, Date to,
			CurrencyTypeEntity typeEntity) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT currency.id, currency.value, currency.currency_type_id, currency.date_entry_id FROM currency "
					+ "LEFT JOIN date_entry ON currency.date_entry_id = date_entry.id "
					+ "WHERE currency_type_id = ? and date_entry.date between ? and ? ");
			statement.setInt(1, typeEntity.getId());
			statement.setDate(2, new java.sql.Date(from.getTime()));
			statement.setDate(3, new java.sql.Date(to.getTime()));

			ResultSet dbResult = statement.executeQuery();
			List<CurrencyEntity> list = new ArrayList<CurrencyEntity>();
			while (dbResult.next()) {
				CurrencyEntity entity = new CurrencyEntity();
				entity.setId(dbResult.getInt(1));
				entity.setValue(dbResult.getDouble(2));

				DateEntryEntity dateEntryEntity = findDateEntry(dbResult
						.getInt(4));
				CurrencyTypeEntity currencyTypeEntity = findCurrencyType(dbResult
						.getInt(3));
				entity.setDate(dateEntryEntity);
				entity.setType(currencyTypeEntity);

				list.add(entity);
			}
			statement.close();
			dbResult.close();
			return list;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public CurrencyTypeEntity insert(CurrencyTypeEntity entity)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("INSERT INTO currency_type(name, acronym) VALUES(?, ?)  RETURNING id");
			statement.setString(1, entity.getName());
			statement.setString(2, entity.getAcronym());

			ResultSet dbResult = statement.executeQuery();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
			} else {
				throw new DAOException("Id of persisted object not found");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Inserting failed "
					+ e.getLocalizedMessage());
		}
	}

	public CurrencyTypeEntity update(CurrencyTypeEntity entity)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("UPDATE currency_type SET name = ?, acronym = ? WHERE id = ?");
			statement.setString(1, entity.getName());
			statement.setString(2, entity.getAcronym());
			statement.setInt(3, entity.getId());

			statement.execute();
			statement.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Updating failed " + e.getLocalizedMessage());
		}
	}

	public CurrencyTypeEntity delete(CurrencyTypeEntity entity)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("DELETE FROM currency_type WHERE id = ?");
			statement.setInt(1, entity.getId());

			statement.execute();
			statement.close();
			entity.setId(null);
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Deleting failed " + e.getLocalizedMessage());
		}
	}

	public CurrencyTypeEntity findCurrencyType(int id) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT id, name, acronym FROM currency_type WHERE id = ?");
			statement.setInt(1, id);

			ResultSet dbResult = statement.executeQuery();
			CurrencyTypeEntity entity = new CurrencyTypeEntity();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
				entity.setName(dbResult.getString(2));
				entity.setAcronym(dbResult.getString(3));
			} else {
				throw new DAOException("Query failed");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public CurrencyTypeEntity findCurrencyType(String acronym)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT id, name, acronym FROM currency_type WHERE acronym = ?");
			statement.setString(1, acronym);

			ResultSet dbResult = statement.executeQuery();
			CurrencyTypeEntity entity = new CurrencyTypeEntity();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
				entity.setName(dbResult.getString(2));
				entity.setAcronym(dbResult.getString(3));
			} else {
				throw new DAOException("Query failed");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public List<CurrencyTypeEntity> findAllCurrencyTypes() throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT id, name, acronym FROM currency_type");

			ResultSet dbResult = statement.executeQuery();
			List<CurrencyTypeEntity> list = new ArrayList<CurrencyTypeEntity>();
			while (dbResult.next()) {
				CurrencyTypeEntity entity = new CurrencyTypeEntity();
				entity.setId(dbResult.getInt(1));
				entity.setName(dbResult.getString(2));
				entity.setAcronym(dbResult.getString(3));

				list.add(entity);
			}
			statement.close();
			dbResult.close();
			return list;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public DateEntryEntity insert(DateEntryEntity entity) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("INSERT INTO date_entry(date) VALUES(?)  RETURNING id");
			statement
					.setDate(1, new java.sql.Date(entity.getValue().getTime()));

			ResultSet dbResult = statement.executeQuery();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
			} else {
				throw new DAOException("Id of persisted object not found");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Inserting failed "
					+ e.getLocalizedMessage());
		}
	}

	public DateEntryEntity update(DateEntryEntity entity) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("UPDATE date_entry SET date = ? WHERE id = ?  RETURNING id");
			statement
					.setDate(1, new java.sql.Date(entity.getValue().getTime()));
			statement.setInt(2, entity.getId());

			statement.execute();
			statement.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Updating failed " + e.getLocalizedMessage());
		}
	}

	public DateEntryEntity delete(DateEntryEntity entity) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("DELETE FROM date_entry WHERE id = ?  RETURNING id");
			statement.setInt(1, entity.getId());

			statement.execute();
			statement.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Deleting failed " + e.getLocalizedMessage());
		}
	}

	public DateEntryEntity findDateEntry(int id) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT id, date FROM date_entry WHERE id = ?");
			statement.setInt(1, id);

			ResultSet dbResult = statement.executeQuery();
			DateEntryEntity entity = new DateEntryEntity();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
				entity.setValue(dbResult.getDate(2));
			} else {
				throw new DAOException("Query failed");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public DateEntryEntity findDateEntry(Date date) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT id, date FROM date_entry WHERE date = ?");
			statement.setDate(1, new java.sql.Date(date.getTime()));

			ResultSet dbResult = statement.executeQuery();
			DateEntryEntity entity = new DateEntryEntity();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
				entity.setValue(dbResult.getDate(2));
			} else {
				throw new DAOException("Query failed");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public List<DateEntryEntity> findAllDateEntries() throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT id, date FROM date_entry");

			ResultSet dbResult = statement.executeQuery();
			List<DateEntryEntity> list = new ArrayList<DateEntryEntity>();
			while (dbResult.next()) {
				DateEntryEntity entity = new DateEntryEntity();
				entity.setId(dbResult.getInt(1));
				entity.setValue(dbResult.getDate(2));

				list.add(entity);
			}
			statement.close();
			dbResult.close();
			return list;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public CurrencyAverageEntity insert(CurrencyAverageEntity entity)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("INSERT INTO currency_average(currency_type_id, value) VALUES(?, ?)  RETURNING id");
			statement.setInt(1, entity.getCurrencyType().getId());
			statement.setDouble(2, entity.getValue());

			ResultSet dbResult = statement.executeQuery();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
			} else {
				throw new DAOException("Id of persisted object not found");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Inserting failed "
					+ e.getLocalizedMessage());
		}
	}

	public CurrencyAverageEntity update(CurrencyAverageEntity entity)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("UPDATE currency_average SET currency_type_id = ?, value = ? WHERE id = ?  RETURNING id");
			statement.setInt(1, entity.getCurrencyType().getId());
			statement.setDouble(2, entity.getValue());
			statement.setInt(3, entity.getId());

			statement.execute();
			statement.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Updating failed " + e.getLocalizedMessage());
		}
	}

	public CurrencyAverageEntity delete(CurrencyAverageEntity entity)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("DELETE FROM currency_average WHERE id = ?  RETURNING id");
			statement.setInt(1, entity.getId());

			statement.execute();
			statement.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Deleting failed " + e.getLocalizedMessage());
		}
	}

	public CurrencyAverageEntity findCurrencyAverage(int id)
			throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT id, currency_type_id, value FROM currency_average WHERE id = ?");
			statement.setInt(1, id);

			ResultSet dbResult = statement.executeQuery();
			CurrencyAverageEntity entity = new CurrencyAverageEntity();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
				entity.setValue(dbResult.getDouble(3));

				CurrencyTypeEntity currencyType = findCurrencyType(dbResult
						.getInt(2));
				entity.setCurrencyType(currencyType);

			} else {
				throw new DAOException("Query failed ");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

	public CurrencyAverageEntity findCurrencyAverage(
			CurrencyTypeEntity currencyTypeEntity) throws DAOException {
		try {
			PreparedStatement statement = getPreparedStatementHardLocked("SELECT id, currency_type_id, value FROM currency_average WHERE currency_type_id = ?");
			statement.setInt(1, currencyTypeEntity.getId());

			ResultSet dbResult = statement.executeQuery();
			CurrencyAverageEntity entity = new CurrencyAverageEntity();
			if (dbResult.next()) {
				entity.setId(dbResult.getInt(1));
				entity.setValue(dbResult.getDouble(3));

				CurrencyTypeEntity currencyType = findCurrencyType(dbResult
						.getInt(2));
				entity.setCurrencyType(currencyType);

			} else {
				throw new DAOException("Query failed");
			}
			statement.close();
			dbResult.close();
			return entity;
		} catch (SQLException e) {
			throw new DAOException("Finding failed " + e.getLocalizedMessage());
		}
	}

}
